package cn.jy.ad.utils;

import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class DatabaseChecker {
    public static Connection getConnection() throws SQLException {
        // 这里配置你的数据库连接信息
        String url = "jdbc:mysql://rm-cn-tk23vozty000c3ao.rwlb.rds.aliyuncs.com:3306/express?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8";
        String username = "yanxi";
        String password = "@aB123456";
        return java.sql.DriverManager.getConnection(url, username, password);
    }

    public static void checkAndUpdateTables() {
        Connection connection = null; // 声明连接变量
        try {
            connection = getConnection(); // 获取数据库连接
            connection.setAutoCommit(false); // 关闭自动提交

            List<Integer> idsToUpdate1 = new ArrayList<>();
            List<Integer> idsToUpdate2 = new ArrayList<>();

            // 检查第一个表并收集需要更新的ID
            String sql1 = "SELECT id, down_date_time FROM express_split_advertising WHERE is_status != 1 and down_date_time <= ?";
            try (PreparedStatement statement1 = connection.prepareStatement(sql1)) {
                statement1.setDate(1, new Date(new java.util.Date().getTime()));
                try (ResultSet resultSet1 = statement1.executeQuery()) {
                    while (resultSet1.next()) {
                        int id = resultSet1.getInt("id");
                        idsToUpdate1.add(id);
                    }
                }
            }

            if (!idsToUpdate1.isEmpty()) {
                String updateSql1 = "UPDATE express_split_advertising SET is_status = 1 WHERE id = ?";
                try (PreparedStatement updateStatement1 = connection.prepareStatement(updateSql1)) {
                    for (int id : idsToUpdate1) {
                        updateStatement1.setInt(1, id);
                        updateStatement1.addBatch(); // 添加到批处理
                    }
                    updateStatement1.executeBatch(); // 执行批处理
                }
            }

            // 检查第二个表并收集需要更新的ID
            String sql2 = "SELECT id, down_date_time FROM express_app_ads WHERE is_status != 1 and down_date_time <= ?";
            try (PreparedStatement statement2 = connection.prepareStatement(sql2)) {
                statement2.setDate(1, new Date(new java.util.Date().getTime()));
                try (ResultSet resultSet2 = statement2.executeQuery()) {
                    while (resultSet2.next()) {
                        int id = resultSet2.getInt("id");
                        idsToUpdate2.add(id);
                    }
                }
            }

            if (!idsToUpdate2.isEmpty()) {
                String updateSql2 = "UPDATE express_app_ads SET is_status = 1 WHERE id = ?";
                try (PreparedStatement updateStatement2 = connection.prepareStatement(updateSql2)) {
                    for (int id : idsToUpdate2) {
                        updateStatement2.setInt(1, id);
                        updateStatement2.addBatch(); // 添加到批处理
                    }
                    updateStatement2.executeBatch(); // 执行批处理
                }
            }

            connection.commit(); // 提交事务
        } catch (SQLException e) {
            e.printStackTrace();
            if (connection != null) {
                try {
                    connection.rollback(); // 回滚事务
                } catch (SQLException rollbackEx) {
                    rollbackEx.printStackTrace();
                }
            }
        } finally {
            if (connection != null) {
                try {
                    connection.close(); // 确保关闭连接
                } catch (SQLException closeEx) {
                    closeEx.printStackTrace();
                }
            }
        }
    }


}